﻿using Logger7.Data;

using Microsoft.Data.SqlClient;

using System.Text;

namespace Logger7;

public class SqlServerLoggerProcessor
{
    private readonly string _ConnectionString;
    private readonly string _TableName;
    [ThreadStatic]
    private static StringBuilder? _LogBuilder;
    public SqlServerLoggerProcessor(string connectionString, bool createTable,string tableName)
    {
        _ConnectionString = connectionString;
        _TableName = tableName;
        if (createTable)
        {
            using var cn = new SqlConnection(_ConnectionString);
            var cmd = cn.CreateCommand();
            cmd.CommandText = $""""
if not Exists(select top 1 * from sysObjects where Id=OBJECT_ID(N'{_TableName}') and xtype='U')
begin
CREATE TABLE [dbo].[{_TableName}](
    [Id] [bigint] IDENTITY(9223372036854775807,-1) NOT NULL,
	[Dt] [datetime] NOT NULL,
    [UserExt] [nvarchar](64) not null,
	[LogLevel] [int] NOT NULL,
	[Name] [nvarchar](256) NOT NULL,
	[Msg] [nvarchar](max) NOT NULL,
	[Ex] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_{_TableName}] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
create index IX_{_TableName}_Dt on {_TableName}(DT)
create index IX_{_TableName}_Name on {_TableName}(Name)
create index IX_{_TableName}_UserExt on {_TableName}(UserExt)
end
"""";
            cn.Open();
            cmd.ExecuteNonQuery();
        }
    }


    public void Write<TState>(string categoryName, LogLevel logLevel, EventId eventId, TState state, Exception? exception, Func<TState, Exception?, string> formatter)
    {
        var logBuilder = _LogBuilder;
        _LogBuilder = null;

        if (logBuilder == null)
            logBuilder = new StringBuilder();
        if (eventId.Id > 0)
            logBuilder.Append("[" + eventId.Id + "]");
        if (!string.IsNullOrEmpty(eventId.Name))
            logBuilder.Append("(" + eventId.Name + ")");
        var msg = formatter(state, exception);
        if (!string.IsNullOrEmpty(msg))
            logBuilder.Append(msg);

        using var cn = new SqlConnection(_ConnectionString);
        var cmd = cn.CreateCommand();
        cmd.CommandText = $"INSERT INTO {_TableName} (Dt,Name,LogLevel,Msg,UserExt,ex)  {(state is SqlServerState ? " OUTPUT inserted.Id" : "")}  VALUES (getdate(),@Name,@LogLevel,@Msg,@UserExt,@Ex)";
        cmd.Parameters.AddWithValue("@Name", categoryName);
        cmd.Parameters.AddWithValue("@LogLevel", (int)logLevel);
        cmd.Parameters.AddWithValue("@Msg", logBuilder.ToString());
        cmd.Parameters.AddWithValue("@UserExt", state is SqlServerState s ? s.UserExt ?? "" : "");
        cmd.Parameters.AddWithValue("@Ex", exception?.ToString() ?? "");
        cn.Open();

        if (state is SqlServerState sqlServerState)
        {
            using var dr = cmd.ExecuteReader();
            dr.Read();
            var id = dr.GetInt64(0);
            sqlServerState.LoggerId = id;
        }
        else
            cmd.ExecuteNonQuery();

        logBuilder.Clear();
        if (logBuilder.Capacity > 1024)
            logBuilder.Capacity = 1024;
        _LogBuilder = logBuilder;
    }


    public async Task<List<TLogger>> ReadAsync(int pageNo, int pageSize, string? userNameExt, string? name, string? searchStr)
    {

        using var cn = new SqlConnection(_ConnectionString);
        var sql = $"SELECT [Id], [Dt], [LogLevel], [Msg], [Name],UserExt,ex FROM [{_TableName}] ";
        List<string> whereItems = new();
        if (!string.IsNullOrEmpty(userNameExt))
            whereItems.Add($"UserNameExt='{userNameExt}'");
        if (!string.IsNullOrEmpty(name))
            whereItems.Add($"name='{name}'");
        if (!string.IsNullOrEmpty(searchStr))
            whereItems.Add($"(msg like '%{searchStr}%' or ex like '%{searchStr}%')");

        if (whereItems.Count > 0)
            sql += " where " + string.Join(" and ", whereItems);
        sql += $" ORDER BY [Id] OFFSET {pageNo * pageSize} ROWS FETCH NEXT {pageSize} ROWS ONLY";

        var cmd = cn.CreateCommand();
        cmd.CommandText = sql;
        //if (!string.IsNullOrEmpty(searchStr) && !string.IsNullOrEmpty(name))
        //    cmd.CommandText = $ where name='{name}' and Msg like '%{searchStr}%' ";
        //else if (!string.IsNullOrEmpty(searchStr))
        //    cmd.CommandText = $"SELECT [Id], [Dt], [LogLevel], [Msg], [Name] FROM [{_TableName}] where Msg like '%{searchStr}%' ORDER BY [Id] OFFSET {pageNo * pageSize} ROWS FETCH NEXT {pageSize} ROWS ONLY";
        //else if (!string.IsNullOrEmpty(name))
        //    cmd.CommandText = $"SELECT [Id], [Dt], [LogLevel], [Msg], [Name] FROM [{_TableName}] where name='{name}'  ORDER BY [Id] OFFSET {pageNo * pageSize} ROWS FETCH NEXT {pageSize} ROWS ONLY";
        //else
        //    cmd.CommandText = $"SELECT [Id], [Dt], [LogLevel], [Msg], [Name] FROM [{_TableName}]  ORDER BY [Id] OFFSET {pageNo * pageSize} ROWS FETCH NEXT {pageSize} ROWS ONLY";
        await cn.OpenAsync();
        using var dr = await cmd.ExecuteReaderAsync();
        List<TLogger> items = new();
        while (await dr.ReadAsync())
        {
            items.Add(new TLogger
            {
                Id = dr.GetInt64(0),
                Dt = dr.GetDateTime(1),
                LogLevel = (LogLevel)dr.GetInt32(2),
                Msg = dr.GetString(3),
                Name = dr.GetString(4),
                UserExt = dr.GetString(5),
                Exception = dr.GetString(6)
            });
        }
        return items;
    }
    public async Task<TLogger?> ReadAsync(long id)
    {

        using var cn = new SqlConnection(_ConnectionString);
        var cmd = cn.CreateCommand();
        cmd.CommandText = $"SELECT [Id], [Dt], [LogLevel], [Msg], [Name],ex FROM [{_TableName}] where id={id}";
        await cn.OpenAsync();
        using var dr = await cmd.ExecuteReaderAsync();
        while (await dr.ReadAsync())
        {
            return new TLogger
            {
                Id = dr.GetInt64(0),
                Dt = dr.GetDateTime(1),
                LogLevel = (LogLevel)dr.GetInt32(2),
                Msg = dr.GetString(3),
                Name = dr.GetString(4),
                Exception = dr.GetString(5)
            };
        }
        return default;
    }
    public async Task<int> DeleteAsync()
    {
        using var cn = new SqlConnection(_ConnectionString);
        var cmd = cn.CreateCommand();
        cmd.CommandText = $"delete [{_TableName}] where dt<'{DateTime.Now.AddDays(-30).ToString("yyyy/MM/dd")}'";
        await cn.OpenAsync();
        return await cmd.ExecuteNonQueryAsync();
    }

    public async Task DeleteAsync(long id)
    {
        using var cn = new SqlConnection(_ConnectionString);
        var cmd = cn.CreateCommand();
        cmd.CommandText = $"delete [{_TableName}] where id={id}";
        await cn.OpenAsync();
        await cmd.ExecuteNonQueryAsync();
    }

}
